package lab03;

import java.sql.*;

public class Test14 {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");//注册驱动程序
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //第二阶段编码，connection,statement,resultset
        Connection connection = null;
        Statement statement = null;

        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/java_2017?serverTimezone=UTC",
                    "root","113223" );//三个参数
            statement = connection.createStatement();
            //创建表
            statement.execute("CREATE TABLE t_userinfo"+"usercode VARCHAR(4) NOT NULL"
                    +" VARCHAR （10）"+"password VARCHAR (6)"
                    +"email VARCHAR (40)"+"primary key(code)");
            //插入数据
            statement.executeUpdate("INSERT INTO t_userinfo(usercode,username,password,email,primaary key)"+
                    "VALUES ('7101','宋晓梅'，'123546','xiaomei@yahoo.com.cn')");
            statement.executeUpdate("INSERT INTO t_userinfo(usercode,username,password,email,primaary key)"+
                    "VALUES ('7102','关松杰'，'654321','songjie@263.net')");
            statement.executeUpdate("INSERT INTO t_userinfo(usercode,username,password,email,primaary key)"+
                    "VALUES ('7103','李晓梅'，'123654','xiaomei@yahoo.com.cn')");
            statement.executeUpdate("INSERT INTO t_userinfo(usercode,username,password,email,primaary key)"+
                    "VALUES ('7104','张岩松'，'456123','songjie@yahoo.com.cn')");
            statement.executeUpdate("INSERT INTO t_userinfo(usercode,username,password,email,primaary key)"+
                    "VALUES ('7105','王亚声'，'321456','yajie@yahoo.com.cn')");
            statement.executeUpdate("INSERT INTO t_userinfo(usercode,username,password,email,primaary key)"+
                    "VALUES ('7106','曲小声'，'654321','xiaosheng@yahoo.com.cn')");
            //输出结果
            ResultSet resultSet = statement.executeQuery("SELECT *FROM t_userinfo");
            while(resultSet.next()){
                System.out.print(resultSet.getString(1)+" ");
                System.out.print(resultSet.getString(2)+" ");
                System.out.print(resultSet.getString(3)+" ");
                System.out.print(resultSet.getString(4)+" ");
            }
            //更新数据
            statement.executeUpdate("INSERT INTO t_userinfo " +
                            "VALUES ('45621','刘翔'，'516484','liuxiang@qq.com')"
                    );
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            statement.close();
             connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
